import { app, shell, BrowserWindow, ipcMain, dialog } from 'electron'
import { join, dirname } from 'path'
import { electronApp, optimizer, is } from '@electron-toolkit/utils'
import icon from '../../resources/icon.png?asset'
import fs from 'fs'
import initSqlJs from 'sql.js'

// 检查是否有管理员权限
function checkAdminRights() {
  try {
    // 尝试在C盘根目录创建一个临时文件来测试权限
    const testPath = 'C:\\admin_test.tmp'
    fs.writeFileSync(testPath, 'test')
    fs.unlinkSync(testPath)
    return true
  } catch (error) {
    console.error('没有管理员权限:', error)
    return false
  }
}

// 数据库文件路径 - 修改为C盘根目录下的固定位置
const dbFolderPath = 'C:\\military_engineering_db'
const dbPath = join(dbFolderPath, 'military_engineering.db')
const backupFolderPath = join(dbFolderPath, 'backup')  // 添加备份文件夹路径

// 创建数据库连接
let db = null

/**
 * 执行自动备份
 */
async function autoBackupDatabase() {
  try {
    // 确保备份目录存在
    if (!fs.existsSync(backupFolderPath)) {
      fs.mkdirSync(backupFolderPath, { recursive: true })
    }

    // 生成备份文件名（使用更简洁的时间格式）
    const now = new Date()
    const timestamp = now.getFullYear() + '-' +
      String(now.getMonth() + 1).padStart(2, '0') + '-' +
      String(now.getDate()).padStart(2, '0') + '-' +
      String(now.getHours()).padStart(2, '0') + '-' +
      String(now.getMinutes()).padStart(2, '0') + '-' +
      String(now.getSeconds()).padStart(2, '0')
    const backupFileName = `military_engineering_backup_${timestamp}.db`
    const backupPath = join(backupFolderPath, backupFileName)

    // 导出数据库并保存到备份文件
    const data = db.export()
    const buffer = Buffer.from(data)
    fs.writeFileSync(backupPath, buffer)

    console.log('数据库自动备份成功:', backupPath)

    // 清理旧的备份文件（只保留最近10个备份）
    const files = fs.readdirSync(backupFolderPath)
    const backupFiles = files
      .filter(file => file.startsWith('military_engineering_backup_'))
      .sort((a, b) => b.localeCompare(a)) // 按文件名降序排序

    // 如果备份文件超过10个，删除多余的
    if (backupFiles.length > 10) {
      backupFiles.slice(10).forEach(file => {
        const filePath = join(backupFolderPath, file)
        fs.unlinkSync(filePath)
        console.log('删除旧的备份文件:', filePath)
      })
    }

    return true
  } catch (error) {
    console.error('数据库自动备份失败:', error)
    return false
  }
}

/**
 * 初始化数据库
 */
async function initDatabase() {
  try {
    // 检查是否有管理员权限
    const hasAdminRights = checkAdminRights()
    
    // 创建数据库目录（如果不存在）
    if (!fs.existsSync(dbFolderPath)) {
      try {
        fs.mkdirSync(dbFolderPath, { recursive: true })
        console.log('创建数据库目录成功:', dbFolderPath)
      } catch (error) {
        console.error('创建数据库目录失败，可能需要管理员权限:', error)
        
        // 如果没有管理员权限，显示提示对话框
        if (!hasAdminRights) {
          dialog.showMessageBoxSync({
            type: 'warning',
            title: '权限不足',
            message: '无法在C盘根目录创建数据库文件夹，请以管理员身份运行本程序。',
            detail: '程序将使用备用位置存储数据库文件。',
            buttons: ['确定']
          })
        }
        
        // 如果创建失败，回退到用户数据目录
        const fallbackPath = join(app.getPath('userData'), 'military_engineering.db')
        console.log('使用备用数据库路径:', fallbackPath)
        return await initDatabaseWithPath(fallbackPath)
      }
    }

    const result = await initDatabaseWithPath(dbPath)
    
    // 初始化成功后执行自动备份
    if (result) {
      await autoBackupDatabase()
    }

    return result
  } catch (error) {
    console.error('数据库初始化失败:', error)
    return false
  }
}

/**
 * 使用指定路径初始化数据库
 * @param {string} path 数据库文件路径
 */
async function initDatabaseWithPath(path) {
  try {
    // 确保目录存在
    const dbDir = dirname(path)
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true })
    }

    // 初始化SQL.js
    const SQL = await initSqlJs();
    
    // 如果文件存在，加载它
    if (fs.existsSync(path)) {
      const data = fs.readFileSync(path);
      db = new SQL.Database(new Uint8Array(data));
      console.log('数据库加载成功:', path);
      
      // 执行数据库迁移
      await migrateDatabase();
    } else {
      // 创建新数据库
      db = new SQL.Database();
      console.log('创建新数据库:', path);
      
      // 创建表结构
      createTables();
      
      // 初始化基础数据
      initBaseData();
    }
    
    // 保存数据库
    saveDatabase(path);

    return true;
  } catch (error) {
    console.error(`使用路径 ${path} 初始化数据库失败:`, error);
    return false;
  }
}

/**
 * 执行数据库迁移
 */
async function migrateDatabase() {
  try {
    // 检查是否需要迁移 project_code 约束
    const tableInfo = db.exec("PRAGMA table_info(projects)");
    const columns = tableInfo[0].values.map(row => row[1]);
    
    // 检查 project_code 是否有约束需要移除
    const projectCodeInfo = tableInfo[0].values.find(row => row[1] === 'project_code');
    if (projectCodeInfo && (projectCodeInfo[3] === 1 || projectCodeInfo[5] === 1)) { // 检查是否有 NOT NULL 或 PRIMARY KEY 约束
      console.log('开始迁移: 移除 project_code 约束');
      
      // 创建临时表
      db.exec(`
        CREATE TABLE projects_temp (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          project_number INTEGER,
          project_code TEXT,
          name TEXT NOT NULL,
          description TEXT,
          location TEXT,
          status TEXT NOT NULL,
          sub_status TEXT,
          progress REAL NOT NULL DEFAULT 0,
          phase_status TEXT,
          task_document_no TEXT,
          planning_type TEXT,
          construction_type TEXT,
          total_investment REAL,
          direct_cost REAL,
          task_issue_date TEXT,
          required_completion_date TEXT,
          extended_completion_date TEXT,
          contract_start_date TEXT,
          contract_end_date TEXT,
          contract_duration INTEGER,
          contract_amount REAL,
          bidding_mode TEXT,
          survey_design_unit TEXT,
          cost_compilation_unit TEXT,
          cost_audit_unit TEXT,
          construction_unit TEXT,
          supervision_unit TEXT,
          using_unit_type TEXT,
          using_unit TEXT,
          supply_unit TEXT,
          field_contact TEXT,
          contact_phone TEXT,
          detail_page_start_time TEXT,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL,
          notes TEXT
        )
      `);
      
      // 复制数据
      db.exec('INSERT INTO projects_temp SELECT * FROM projects');
      
      // 删除旧表
      db.exec('DROP TABLE projects');
      
      // 重命名临时表
      db.exec('ALTER TABLE projects_temp RENAME TO projects');
      
      // 重新创建必要的索引
      db.exec(`
        CREATE INDEX idx_projects_status ON projects (status);
        CREATE INDEX idx_projects_progress ON projects (progress);
        CREATE INDEX idx_projects_project_number ON projects (project_number);
      `);
      
      console.log('迁移完成: 移除 project_code 约束');
    }

    // 更新阶段状态选项
    console.log('开始更新: 阶段状态选项');
    const phaseStatusResult = db.exec("SELECT value FROM settings WHERE key = 'default_phase_status'");
    if (phaseStatusResult[0]) {
      const currentValue = phaseStatusResult[0].values[0][0];
      if (!currentValue.includes('其他')) {
        // 更新阶段状态选项，添加"其他"选项
        db.exec(`
          UPDATE settings 
          SET value = '正常进行,阶段性迟缓,滞后,其他',
              updated_at = datetime('now')
          WHERE key = 'default_phase_status'
        `);
        console.log('阶段状态选项更新完成');
      }
    }

    // 添加 using_unit_type 字段
    if (!columns.includes('using_unit_type')) {
      console.log('开始迁移: 添加 using_unit_type 字段');
      
      // 添加新字段
      db.exec('ALTER TABLE projects ADD COLUMN using_unit_type TEXT');
      
      // 根据现有的 using_unit 值设置默认的 using_unit_type
      // db.exec(`
      //   UPDATE projects 
      //   SET using_unit_type = CASE
      //     WHEN using_unit = '中心及下属单位' THEN '中心及下属单位'
      //     WHEN using_unit = '代供单位' THEN '代供单位'
      //     WHEN using_unit IS NOT NULL THEN '其他'
      //     ELSE NULL
      //   END
      // `);
      
      console.log('迁移完成: 添加 using_unit_type 字段');
    }
    
    // 添加: 迁移进度更新表结构
    await migrateProgressUpdatesTable();
    
    // 添加: 迁移进度更新表数据结构
    await migrateProgressUpdatesData();
  } catch (error) {
    console.error('数据库迁移失败:', error);
    throw error;
  }
}

/**
 * 迁移进度更新表结构
 * 检查并添加缺失的字段
 */
async function migrateProgressUpdatesTable() {
  try {
    console.log('开始迁移: 进度更新表结构');
    
    // 检查progress_updates表是否存在
    const tableExistsResult = db.exec("SELECT name FROM sqlite_master WHERE type='table' AND name='progress_updates'");
    if (!tableExistsResult[0] || tableExistsResult[0].values.length === 0) {
      console.log('进度更新表不存在，将在需要时创建');
      return false;
    }
    
    // 检查表结构
    const tableInfo = db.exec("PRAGMA table_info(progress_updates)");
    if (!tableInfo[0]) {
      console.log('无法获取进度更新表结构');
      return false;
    }
    
    const columns = tableInfo[0].values.map(row => row[1]);
    console.log('当前进度更新表字段:', columns.join(', '));
    
    // 检查并添加缺失的字段
    const missingColumns = [];
    
    // 检查week_number字段
    if (!columns.includes('week_number')) {
      missingColumns.push(`ALTER TABLE progress_updates ADD COLUMN week_number INTEGER`);
    }
    
    // 检查visual_progress字段
    if (!columns.includes('visual_progress')) {
      missingColumns.push(`ALTER TABLE progress_updates ADD COLUMN visual_progress TEXT`);
    }
    
    // 检查payment_progress字段
    if (!columns.includes('payment_progress')) {
      missingColumns.push(`ALTER TABLE progress_updates ADD COLUMN payment_progress TEXT`);
    }
    
    // 执行添加字段操作
    if (missingColumns.length > 0) {
      console.log(`需要添加 ${missingColumns.length} 个字段`);
      for (const sql of missingColumns) {
        try {
          db.exec(sql);
          console.log(`执行SQL成功: ${sql}`);
        } catch (error) {
          console.error(`执行SQL失败: ${sql}`, error);
          throw error;
        }
      }
      console.log('字段添加完成');
    } else {
      console.log('没有需要添加的字段');
    }
    
    return true;
  } catch (error) {
    console.error('迁移进度更新表结构失败:', error);
    throw error;
  }
}

/**
 * 迁移进度更新表数据结构
 * 创建新的表结构并迁移数据
 */
async function migrateProgressUpdatesData() {
  try {
    console.log('开始迁移: 进度更新表数据结构');
    
    // 检查是否已完成迁移
    const tableInfo = db.exec("PRAGMA table_info(progress_updates)");
    if (!tableInfo[0]) {
      console.log('无法获取进度更新表结构');
      return false;
    }
    
    const columns = tableInfo[0].values.map(row => row[1]);
    
    // 检查是否还有旧字段名
    const hasOldColumns = columns.includes('project_status') || 
                          columns.includes('new_progress') || 
                          columns.includes('status_comment');
    
    if (!hasOldColumns) {
      console.log('进度更新表已经使用新字段名，无需迁移');
      return true;
    }
    
    console.log('检测到旧字段名，开始创建临时表并迁移数据');
    
    // 创建临时表
    db.exec(`
      CREATE TABLE IF NOT EXISTS progress_updates_temp (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id INTEGER NOT NULL,
        update_date TEXT NOT NULL,
        week_number INTEGER,
        status TEXT,              
        progress REAL DEFAULT 0,  
        phase_status TEXT,        
        current_progress TEXT,    
        next_plan TEXT,           
        issues TEXT,              
        visual_progress TEXT,     
        solutions TEXT,           
        payment_progress TEXT,    
        payment_percentage REAL DEFAULT 0, 
        notes TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
      );
    `);
    
    // 迁移数据，将原字段映射到新字段
    db.exec(`
      INSERT INTO progress_updates_temp (
        id, project_id, update_date, 
        status, progress, phase_status, 
        current_progress, next_plan, issues, 
        solutions, payment_percentage, notes,
        created_at, updated_at
      )
      SELECT 
        id, project_id, update_date, 
        project_status, new_progress, phase_status, 
        current_progress, next_plan, issues, 
        solutions, payment_percentage, status_comment,
        created_at, updated_at
      FROM progress_updates;
    `);
    
    // 计算迁移的记录数
    const countResult = db.exec("SELECT COUNT(*) FROM progress_updates_temp");
    const migratedCount = countResult[0].values[0][0];
    console.log(`已迁移 ${migratedCount} 条进度更新记录`);
    
    // 删除原表
    db.exec("DROP TABLE progress_updates");
    
    // 重命名临时表
    db.exec("ALTER TABLE progress_updates_temp RENAME TO progress_updates");
    
    // 重建索引
    db.exec(`
      CREATE INDEX IF NOT EXISTS idx_progress_updates_project_id ON progress_updates (project_id);
      CREATE INDEX IF NOT EXISTS idx_progress_updates_update_date ON progress_updates (update_date);
    `);
    
    console.log('进度更新表数据迁移完成');
    return true;
  } catch (error) {
    console.error('迁移进度更新表数据失败:', error);
    throw error;
  }
}

/**
 * 保存数据库到文件
 * @param {string} path 保存路径
 */
function saveDatabase(path) {
  try {
    if (!db) {
      throw new Error('数据库未初始化');
    }
    
    const data = db.export();
    const buffer = Buffer.from(data);
    fs.writeFileSync(path, buffer);
    console.log('数据库保存成功:', path);
  } catch (error) {
    console.error('保存数据库失败:', error);
  }
}

/**
 * 创建数据库表
 */
function createTables() {
  // 创建项目表
  db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_number INTEGER,
      project_code TEXT,
      name TEXT NOT NULL,
      description TEXT,
      location TEXT,
      status TEXT NOT NULL,
      sub_status TEXT,
      progress REAL NOT NULL DEFAULT 0,
      phase_status TEXT,
      
      -- 任务书信息
      task_document_no TEXT,
      planning_type TEXT,
      construction_type TEXT,  -- 建设方式（统建/代建）
      total_investment REAL,
      direct_cost REAL,
      task_issue_date TEXT,
      required_completion_date TEXT,
      extended_completion_date TEXT,
      
      -- 合同信息
      contract_start_date TEXT,
      contract_end_date TEXT,
      contract_duration INTEGER,
      contract_amount REAL,
      bidding_mode TEXT,
      
      -- 参建单位信息
      survey_design_unit TEXT,
      cost_compilation_unit TEXT,
      cost_audit_unit TEXT,
      construction_unit TEXT,
      supervision_unit TEXT,
      using_unit_type TEXT,
      using_unit TEXT,
      supply_unit TEXT,
      
      -- 联系信息
      field_contact TEXT,
      contact_phone TEXT,
      
      detail_page_start_time TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      notes TEXT
    );
  `);

  // 创建里程碑节点表
  db.exec(`
    CREATE TABLE IF NOT EXISTS milestones (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      update_id INTEGER,
      name TEXT NOT NULL,
      category TEXT NOT NULL,
      planned_date TEXT,
      actual_date TEXT,
      is_custom INTEGER DEFAULT 0,
      notes TEXT,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
      FOREIGN KEY (update_id) REFERENCES progress_updates (id) ON DELETE SET NULL
    );
  `);

  // 创建进度更新表
  db.exec(`
    CREATE TABLE IF NOT EXISTS progress_updates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      update_date TEXT NOT NULL,
      week_number INTEGER,
      status TEXT,              -- 项目阶段
      progress REAL DEFAULT 0,  -- 施工进度百分比
      phase_status TEXT,        -- 阶段状态
      current_progress TEXT,    -- 本周主要工作
      next_plan TEXT,           -- 下周工作计划
      issues TEXT,              -- 矛盾问题
      visual_progress TEXT,     -- 形象进度
      solutions TEXT,           -- 解决措施
      payment_progress TEXT,    -- 工程款支付情况文字描述
      payment_percentage REAL DEFAULT 0, -- 付款进度百分比
      notes TEXT,               -- 备注信息/状态说明
      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
    );
  `);

  // 创建系统设置表
  db.exec(`
    CREATE TABLE IF NOT EXISTS settings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      key TEXT NOT NULL UNIQUE,
      value TEXT,
      description TEXT,
      category TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
  `);

  // 创建索引
  db.exec(`
    -- 项目表索引
    CREATE INDEX IF NOT EXISTS idx_projects_status ON projects (status);
    CREATE INDEX IF NOT EXISTS idx_projects_progress ON projects (progress);
    CREATE INDEX IF NOT EXISTS idx_projects_project_number ON projects (project_number);
    
    -- 里程碑节点表索引
    CREATE INDEX IF NOT EXISTS idx_milestones_project_id ON milestones (project_id);
    CREATE INDEX IF NOT EXISTS idx_milestones_category ON milestones (category);
    CREATE INDEX IF NOT EXISTS idx_milestones_is_custom ON milestones (is_custom);
    CREATE INDEX IF NOT EXISTS idx_milestones_progress_update_id ON milestones (update_id);
    
    -- 进度更新表索引
    CREATE INDEX IF NOT EXISTS idx_progress_updates_project_id ON progress_updates (project_id);
    CREATE INDEX IF NOT EXISTS idx_progress_updates_update_date ON progress_updates (update_date);
  `);
}

/**
 * 初始化基础数据
 */
function initBaseData() {
  // 检查是否已有数据
  const result = db.exec("SELECT COUNT(*) FROM projects");
  const count = result[0]?.values[0][0] || 0;
  
  if (count > 0) {
    console.log('数据库已有数据，跳过初始化');
    return;
  }
  
  console.log('初始化基础数据...');
  
  // 插入系统设置
  insertSettings();
  
  // 插入示例项目
  insertSampleProjects();
}

/**
 * 插入示例项目数据
 */
function insertSampleProjects() {
  // 插入示例项目1 - 在建项目
  db.exec(`
    INSERT INTO projects (
      project_number, project_code, name, description, location, status, 
      progress, phase_status, task_document_no, planning_type, construction_type,
      total_investment, direct_cost, task_issue_date, required_completion_date,
      contract_start_date, contract_end_date, contract_duration, contract_amount,
      bidding_mode, survey_design_unit, construction_unit, supervision_unit, using_unit_type, using_unit,
      field_contact, contact_phone, created_at, updated_at
    ) VALUES (
      1, 'GC2025001', '某部队营房改造工程', '对现有营房进行全面改造升级，提升住宿条件和功能性', 
      '四川成都', '施工-在建', 65, '正常进行', 'JS[2025]001号', '年度计划', '统建',
      1200, 980, '2025-01-10', '2025-12-31', 
      '2025-03-01', '2025-10-31', 245, 1150,
      '招标', '某设计院', '某建筑公司', '某监理公司', '其他', '某部队',
      '张工', '13800138001', datetime('now'), datetime('now')
    );
  `);
  
  // 获取项目1的ID
  const result1 = db.exec("SELECT last_insert_rowid()");
  const project1Id = result1[0].values[0][0];
  
  // 插入示例项目2 - 前期项目
  db.exec(`
    INSERT INTO projects (
      project_number, project_code, name, description, location, status, 
      progress, phase_status, task_document_no, planning_type, construction_type,
      total_investment, direct_cost, task_issue_date, required_completion_date,
      bidding_mode, survey_design_unit, using_unit_type, using_unit,
      field_contact, contact_phone, created_at, updated_at
    ) VALUES (
      2, 'GC2025002', '某训练场地建设项目', '新建现代化训练场地，包含多功能训练区域和配套设施', 
      '江苏南京', '前期-施工图设计及清单编制', 0, '正常进行', 'JS[2025]002号', '专项计划', '代建',
      2500, 2100, '2025-02-15', '2026-06-30', 
      '招标', '某设计研究院', '中心及下属单位', '某训练基地',
      '李工', '13800138002', datetime('now'), datetime('now')
    );
  `);
  
  // 获取项目2的ID
  const result2 = db.exec("SELECT last_insert_rowid()");
  const project2Id = result2[0].values[0][0];
  
  // 为项目1添加里程碑
  db.exec(`
    INSERT INTO milestones (project_id, name, category, planned_date, actual_date, is_custom, created_at, updated_at)
    VALUES 
    (${project1Id}, '任务书下达', '前期', '2025-01-10', '2025-01-10', 0, datetime('now'), datetime('now')),
    (${project1Id}, '方案设计确认', '前期', '2025-01-25', '2025-01-28', 0, datetime('now'), datetime('now')),
    (${project1Id}, '初步设计确认', '前期', '2025-02-10', '2025-02-15', 0, datetime('now'), datetime('now')),
    (${project1Id}, '完成施工图审查', '前期', '2025-02-25', '2025-02-28', 0, datetime('now'), datetime('now')),
    (${project1Id}, '招标公告发布', '发包', '2025-03-05', '2025-03-05', 0, datetime('now'), datetime('now')),
    (${project1Id}, '开标时间', '发包', '2025-03-15', '2025-03-15', 0, datetime('now'), datetime('now')),
    (${project1Id}, '合同签订时间', '发包', '2025-03-25', '2025-03-25', 0, datetime('now'), datetime('now')),
    (${project1Id}, '开工令时间', '施工', '2025-04-01', '2025-04-01', 0, datetime('now'), datetime('now')),
    (${project1Id}, '初步验收时间', '施工', '2025-10-15', null, 0, datetime('now'), datetime('now')),
    (${project1Id}, '竣工验收时间', '竣工', '2025-10-31', null, 0, datetime('now'), datetime('now')),
    (${project1Id}, '结算时间', '竣工', '2025-12-15', null, 0, datetime('now'), datetime('now'));
  `);
  
  // 为项目2添加里程碑
  db.exec(`
    INSERT INTO milestones (project_id, name, category, planned_date, actual_date, is_custom, created_at, updated_at)
    VALUES 
    (${project2Id}, '任务书下达', '前期', '2025-02-15', '2025-02-15', 0, datetime('now'), datetime('now')),
    (${project2Id}, '方案设计确认', '前期', '2025-03-01', '2025-03-05', 0, datetime('now'), datetime('now')),
    (${project2Id}, '初步设计确认', '前期', '2025-03-20', '2025-03-25', 0, datetime('now'), datetime('now')),
    (${project2Id}, '完成施工图审查', '前期', '2025-04-15', null, 0, datetime('now'), datetime('now')),
    (${project2Id}, '完成清单编制', '前期', '2025-05-01', null, 0, datetime('now'), datetime('now'));
  `);
  
  // 为项目1添加进度更新
  db.exec(`
    INSERT INTO progress_updates (project_id, update_date, week_number, current_progress, next_plan, issues, solutions, payment_progress)
    VALUES 
    (${project1Id}, '2025-05-15', 20, '完成基础施工和主体结构30%', '继续主体结构施工', '雨季施工受阻', '调整施工计划，加强排水措施', '已支付合同款30%'),
    (${project1Id}, '2025-06-15', 24, '主体结构完成60%，开始内部管线铺设', '完成主体结构，继续内部管线施工', '部分材料供应延迟', '协调供应商加快发货', '已支付合同款45%'),
    (${project1Id}, '2025-07-15', 28, '主体结构完成90%，内部管线完成50%', '完成主体结构和内部管线施工', '无重大问题', '继续按计划推进', '已支付合同款60%');
  `);
  
  console.log('示例项目数据插入完成');
}

/**
 * 插入系统设置数据
 */
function insertSettings() {
  // 预定义项目状态
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_project_status', 
            '前期-方案设计,前期-施工图设计及清单编制,前期-清单审核,发包-发包方案编制,发包-发包组织实施,发包-评标结果公示,施工-待开工,施工-在建,施工-初步验收,竣工-竣工验收,竣工-实体移交,竣工-完成结算,竣工-完成决算,撤项', 
            '默认项目阶段选项', 
            'project', 
            datetime('now'));
  `);

  // 预定义阶段状态
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_phase_status', 
            '正常进行,阶段性迟缓,滞后', 
            '默认阶段状态选项', 
            'project', 
            datetime('now'));
  `);

  // 预定义里程碑分类
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_milestone_categories', 
            '前期,发包,施工,竣工', 
            '默认里程碑分类', 
            'milestone', 
            datetime('now'));
  `);

  // 预定义发包模式
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_bidding_modes', 
            '招标,直接委托', 
            '默认发包模式', 
            'contract', 
            datetime('now'));
  `);

  // 预定义里程碑节点
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_milestones_early', 
            '任务书下达,方案设计确认,初步设计确认,完成施工图审查,完成清单编制,完成清单审核,施工图确认', 
            '前期阶段默认节点', 
            'milestone', 
            datetime('now'));
  `);

  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_milestones_bidding', 
            '企业邀请/招标公告发布,企业报名时间,开标时间,定标时间,中标通知书发放时间,合同签订时间,直接委托', 
            '发包阶段默认节点', 
            'milestone', 
            datetime('now'));
  `);

  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_milestones_construction', 
            '质监注册时间,施工许可证时间,开工令时间,初步验收时间', 
            '施工阶段默认节点', 
            'milestone', 
            datetime('now'));
  `);

  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_milestones_completion', 
            '档案验收时间,竣工验收时间,实体移交时间,档案移交时间,竣工备案时间,结算时间,决算时间', 
            '竣工阶段默认节点', 
            'milestone', 
            datetime('now'));
  `);

  // 基本UI设置
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('theme_color', 
            '#1890ff', 
            '系统主题色', 
            'ui', 
            datetime('now'));
  `);

  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('font_size', 
            'medium', 
            '系统字体大小（small/medium/large）', 
            'ui', 
            datetime('now'));
  `);

  // 建设方式选项
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_construction_types', 
            '统建,代建', 
            '默认建设方式选项', 
            'project', 
            datetime('now'));
  `);

  // 使用单位选项
  db.exec(`
    INSERT INTO settings (key, value, description, category, updated_at)
    VALUES ('default_using_unit_options', 
            '中心及下属单位,代供单位', 
            '默认使用单位选项（自定义输入不在此列表中）', 
            'project', 
            datetime('now'));
  `);

  console.log('系统设置数据初始化完成');
}

/**
 * 获取数据库连接
 * @returns {Object} 数据库连接对象
 */
function getDb() {
  if (!db) {
    throw new Error('数据库未初始化')
  }
  return db
}

/**
 * 关闭数据库连接
 */
function closeDatabase() {
  if (db) {
    try {
      // 保存数据库
      saveDatabase(dbPath);
      
      // 关闭数据库
      db.close();
      console.log('数据库连接已关闭');
    } catch (error) {
      console.error('关闭数据库连接失败:', error);
    } finally {
      db = null;
    }
  }
}

function createWindow() {
  const mainWindow = new BrowserWindow({
    width: 1280,
    height: 800,
    show: false,
    autoHideMenuBar: true,
    ...(process.platform === 'linux' ? { icon } : {}),
    webPreferences: {
      preload: join(__dirname, '../preload/index.js'),
      sandbox: false,
      nodeIntegration: true,
      contextIsolation: false,
      // 允许打开开发者工具
      devTools: true
    }
  })

  // 添加右键菜单"检查元素"功能
  mainWindow.webContents.on('context-menu', (_, params) => {
    const { Menu } = require('electron')
    
    // 创建一个完整的上下文菜单
    const menuTemplate = []
    
    // 如果在文本上右击，添加文本操作
    if (params.isEditable || params.selectionText) {
      // 如果有选中文本，添加复制选项
      if (params.selectionText) {
        menuTemplate.push({
          label: '复制',
          role: 'copy'
        })
      }
      
      // 如果在可编辑区域，添加粘贴、剪切、全选等选项
      if (params.isEditable) {
        if (params.selectionText) {
          menuTemplate.push({
            label: '剪切',
            role: 'cut'
          })
        }
        
        menuTemplate.push({
          label: '粘贴',
          role: 'paste'
        })
        
        menuTemplate.push({
          label: '全选',
          role: 'selectAll'
        })
      }
      
      menuTemplate.push({ type: 'separator' })
    }
    
    // 添加一个分隔符和检查元素选项（开发调试用）
    menuTemplate.push({
      label: '检查元素',
      click: () => {
        mainWindow.webContents.inspectElement(params.x, params.y)
      }
    })
    
    // 创建并显示菜单
    const menu = Menu.buildFromTemplate(menuTemplate)
    menu.popup()
  })

  // 添加窗口焦点事件监听
  mainWindow.on('focus', () => {
    console.log('[Window Focus] Window gained focus')
    mainWindow.webContents.send('window-focus-changed', true)
  })

  mainWindow.on('blur', () => {
    console.log('[Window Focus] Window lost focus')
    mainWindow.webContents.send('window-focus-changed', false)
  })

  // 监听渲染进程的准备状态
  mainWindow.webContents.on('did-finish-load', () => {
    console.log('[Window State] Content finished loading')
  })

  mainWindow.webContents.on('dom-ready', () => {
    console.log('[Window State] DOM is ready')
  })

  // 添加快捷键监听
  mainWindow.webContents.on('before-input-event', (event, input) => {
    if (input.key === 'F12') {
      mainWindow.webContents.toggleDevTools()
      event.preventDefault()
    }
  })

  // 隐藏菜单栏
  mainWindow.setMenuBarVisibility(false)
  
  // 隐藏顶部版本信息栏
  const { Menu } = require('electron')
  Menu.setApplicationMenu(null)

  mainWindow.on('ready-to-show', () => {
    mainWindow.show()
  })

  mainWindow.webContents.setWindowOpenHandler((details) => {
    shell.openExternal(details.url)
    return { action: 'deny' }
  })

  // HMR for renderer base on electron-vite cli.
  // Load the remote URL for development or the local html file for production.
  if (is.dev && process.env['ELECTRON_RENDERER_URL']) {
    mainWindow.loadURL(process.env['ELECTRON_RENDERER_URL'])
  } else {
    mainWindow.loadFile(join(__dirname, '../renderer/index.html'))
  }
}

// 初始化数据库API
function initDatabaseAPI() {
  // 获取所有项目
  ipcMain.handle('get-projects', async () => {
    try {
      const db = getDb();
      const result = db.exec('SELECT * FROM projects ORDER BY project_number');
      
      // 转换结果格式
      const projects = result[0] ? result[0].values.map(row => {
        const obj = {};
        result[0].columns.forEach((col, i) => {
          obj[col] = row[i];
        });
        return obj;
      }) : [];
      
      return { success: true, data: projects };
    } catch (error) {
      console.error('获取项目列表失败:', error);
      return { success: false, error: error.message };
    }
  });

  // 获取单个项目详情
  ipcMain.handle('get-project', async (_, projectId) => {
    try {
      const db = getDb();
      
      // 获取项目信息
      const projectResult = db.exec(`SELECT * FROM projects WHERE id = ${projectId}`);
      if (!projectResult[0] || projectResult[0].values.length === 0) {
        return { success: false, error: '项目不存在' };
      }
      
      // 转换项目数据格式
      const project = {};
      projectResult[0].columns.forEach((col, i) => {
        project[col] = projectResult[0].values[0][i];
      });
      
      // 获取项目里程碑
      const milestonesResult = db.exec(`SELECT * FROM milestones WHERE project_id = ${projectId} ORDER BY category, planned_date`);
      const milestones = milestonesResult[0] ? milestonesResult[0].values.map(row => {
        const obj = {};
        milestonesResult[0].columns.forEach((col, i) => {
          obj[col] = row[i];
        });
        return obj;
      }) : [];
      
      // 获取项目进度更新
      const progressUpdatesResult = db.exec(`SELECT * FROM progress_updates WHERE project_id = ${projectId} ORDER BY update_date DESC`);
      const progressUpdates = progressUpdatesResult[0] ? progressUpdatesResult[0].values.map(row => {
        const obj = {};
        progressUpdatesResult[0].columns.forEach((col, i) => {
          obj[col] = row[i];
        });
        return obj;
      }) : [];
      
      return { 
        success: true, 
        data: {
          project,
          milestones,
          progressUpdates
        }
      };
    } catch (error) {
      console.error('获取项目详情失败:', error);
      return { success: false, error: error.message };
    }
  });

  // 获取系统设置
  ipcMain.handle('get-settings', async (_, category) => {
    try {
      const db = getDb();
      let query = 'SELECT * FROM settings';
      
      if (category) {
        query += ` WHERE category = '${category}'`;
      }
      
      const result = db.exec(query);
      const settings = result[0] ? result[0].values.map(row => {
        const obj = {};
        result[0].columns.forEach((col, i) => {
          obj[col] = row[i];
        });
        return obj;
      }) : [];
      
      return { success: true, data: settings };
    } catch (error) {
      console.error('获取系统设置失败:', error);
      return { success: false, error: error.message };
    }
  });

  // 创建新项目
  ipcMain.handle('create-project', async (_, projectData) => {
    try {
      const db = getDb();
      
      // 准备当前时间
      const now = new Date().toISOString();
      
      // 构建SQL语句
      const sql = `
        INSERT INTO projects (
          project_number, project_code, name, description, location, status, 
          sub_status, progress, phase_status, task_document_no, planning_type, 
          total_investment, direct_cost, task_issue_date, required_completion_date,
          extended_completion_date, contract_start_date, contract_end_date, 
          contract_duration, contract_amount, bidding_mode, construction_type,
          survey_design_unit, cost_compilation_unit, cost_audit_unit, construction_unit, 
          supervision_unit, using_unit_type, using_unit, supply_unit, field_contact, 
          contact_phone, notes, created_at, updated_at
        ) VALUES (
          ${projectData.project_number || 'NULL'},
          ${projectData.project_code ? `'${projectData.project_code}'` : 'NULL'},  // 允许为空
          '${projectData.name}',
          ${projectData.description ? `'${projectData.description}'` : 'NULL'},
          ${projectData.location ? `'${projectData.location}'` : 'NULL'},
          '${projectData.status}',
          ${projectData.sub_status ? `'${projectData.sub_status}'` : 'NULL'},
          ${projectData.progress || 0},
          ${projectData.phase_status ? `'${projectData.phase_status}'` : 'NULL'},
          ${projectData.task_document_no ? `'${projectData.task_document_no}'` : 'NULL'},
          ${projectData.planning_type ? `'${projectData.planning_type}'` : 'NULL'},
          ${projectData.total_investment || 'NULL'},
          ${projectData.direct_cost || 'NULL'},
          ${projectData.task_issue_date ? `'${projectData.task_issue_date}'` : 'NULL'},
          ${projectData.required_completion_date ? `'${projectData.required_completion_date}'` : 'NULL'},
          ${projectData.extended_completion_date ? `'${projectData.extended_completion_date}'` : 'NULL'},
          ${projectData.contract_start_date ? `'${projectData.contract_start_date}'` : 'NULL'},
          ${projectData.contract_end_date ? `'${projectData.contract_end_date}'` : 'NULL'},
          ${projectData.contract_duration || 'NULL'},
          ${projectData.contract_amount || 'NULL'},
          ${projectData.bidding_mode ? `'${projectData.bidding_mode}'` : 'NULL'},
          ${projectData.construction_type ? `'${projectData.construction_type}'` : 'NULL'},
          ${projectData.survey_design_unit ? `'${projectData.survey_design_unit}'` : 'NULL'},
          ${projectData.cost_compilation_unit ? `'${projectData.cost_compilation_unit}'` : 'NULL'},
          ${projectData.cost_audit_unit ? `'${projectData.cost_audit_unit}'` : 'NULL'},
          ${projectData.construction_unit ? `'${projectData.construction_unit}'` : 'NULL'},
          ${projectData.supervision_unit ? `'${projectData.supervision_unit}'` : 'NULL'},
          ${projectData.using_unit_type ? `'${projectData.using_unit_type}'` : 'NULL'},
          ${projectData.using_unit ? `'${projectData.using_unit}'` : 'NULL'},
          ${projectData.supply_unit ? `'${projectData.supply_unit}'` : 'NULL'},
          ${projectData.field_contact ? `'${projectData.field_contact}'` : 'NULL'},
          ${projectData.contact_phone ? `'${projectData.contact_phone}'` : 'NULL'},
          ${projectData.notes ? `'${projectData.notes}'` : 'NULL'},
          ${now},
          ${now}
        );
      `;
      
      // 执行SQL语句
      db.exec(sql);
      
      // 获取新插入项目的ID
      const result = db.exec("SELECT last_insert_rowid()");
      const projectId = result[0].values[0][0];
      
      return { success: true, data: { projectId } };
    } catch (error) {
      console.error('创建项目失败:', error);
      return { success: false, error: error.message };
    }
  });

  // 提交项目进度更新
  ipcMain.handle('submit-progress-update', async (_, updateData) => {
    try {
      const db = getDb();
      
      // 开始事务
      db.exec('BEGIN TRANSACTION');
      
      // 检查并转换字段名，实现字段名称兼容
      const compatData = {
        project_id: updateData.project_id,
        update_date: updateData.update_date,
        status: updateData.project_status || updateData.status, // 新字段名: status
        progress: updateData.new_progress || updateData.progress || 0, // 新字段名: progress
        phase_status: updateData.phase_status,
        current_progress: updateData.current_progress,
        next_plan: updateData.next_plan,
        issues: updateData.issues,
        solutions: updateData.solutions,
        payment_progress: updateData.payment_progress || '',
        payment_percentage: updateData.payment_percentage || 0,
        notes: updateData.status_comment || updateData.notes || '', // 新字段名: notes
        visual_progress: updateData.visual_progress || '',
        week_number: updateData.week_number || null
      };
      
      // 1. 插入进度更新记录
      const stmt = db.prepare(`
        INSERT INTO progress_updates (
          project_id, update_date, status, progress, 
          phase_status, current_progress, next_plan, 
          issues, solutions, payment_progress, payment_percentage,
          notes, visual_progress, week_number,
          created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
                 datetime('now'), datetime('now'))
      `);
      
      stmt.run([
        compatData.project_id,
        compatData.update_date,
        compatData.status,
        compatData.progress,
        compatData.phase_status,
        compatData.current_progress,
        compatData.next_plan,
        compatData.issues,
        compatData.solutions,
        compatData.payment_progress,
        compatData.payment_percentage,
        compatData.notes,
        compatData.visual_progress,
        compatData.week_number
      ]);
      
      // 获取插入的更新记录ID
      const updateId = db.exec('SELECT last_insert_rowid() as id')[0].values[0][0];
      
      // 2. 更新项目的当前状态和进度
      db.exec(`
        UPDATE projects 
        SET status = '${compatData.status}',
            progress = ${compatData.progress || 0},
            phase_status = '${compatData.phase_status}',
            updated_at = datetime('now')
        WHERE id = ${compatData.project_id}
      `);
      
      // 3. 如果有里程碑数据，则插入里程碑记录
      if (updateData.milestones && updateData.milestones.length > 0) {
        const milestonesStmt = db.prepare(`
          INSERT INTO milestones (
            project_id, update_id, category, name, 
            is_custom, planned_date, actual_date,
            created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))
        `);
        
        updateData.milestones.forEach(milestone => {
          milestonesStmt.run([
            compatData.project_id,
            updateId,
            milestone.category,
            milestone.name,
            milestone.is_custom ? 1 : 0,
            milestone.planned_date || null,
            milestone.actual_date || null
          ]);
        });
        
        milestonesStmt.free();
      }
      
      // 提交事务
      db.exec('COMMIT');
      
      // 保存数据库
      saveDatabase(dbPath);
      
      return { 
        success: true, 
        updateId: updateId,
        message: '进度更新成功'
      };
    } catch (error) {
      // 发生错误时回滚事务
      db.exec('ROLLBACK');
      console.error('提交进度更新失败:', error);
      return { success: false, error: error.message };
    }
  });

 // 获取项目统计数据
 ipcMain.handle('get-project-stats', async () => {
  try {
    const db = getDb();
    
    // 总项目数
    const totalProjectsResult = db.exec('SELECT COUNT(*) as count FROM projects');
    const totalProjects = totalProjectsResult[0].values[0][0];
    
    // 在建项目数
    const inProgressProjectsResult = db.exec("SELECT COUNT(*) as count FROM projects WHERE status LIKE '施工-%'");
    const inProgressProjects = inProgressProjectsResult[0].values[0][0];
    
    // 已完成项目数
    const completedProjectsResult = db.exec("SELECT COUNT(*) as count FROM projects WHERE status LIKE '竣工-%'");
    const completedProjects = completedProjectsResult[0].values[0][0];
    
    // 项目阶段分布
    const phaseDistributionResult = db.exec(`
      SELECT 
        CASE 
          WHEN status LIKE '前期-%' THEN '前期' 
          WHEN status LIKE '发包-%' THEN '发包' 
          WHEN status LIKE '施工-%' THEN '施工' 
          WHEN status LIKE '竣工-%' THEN '竣工' 
        END as phase,
        COUNT(*) as count
      FROM projects
      GROUP BY phase
    `);
    
    const phaseDistribution = phaseDistributionResult[0] ? phaseDistributionResult[0].values.map(row => {
      return {
        phase: row[0],
        count: row[1]
      };
    }) : [];
    
    // 项目状态分布
    const statusDistributionResult = db.exec(`
      SELECT phase_status as status, COUNT(*) as count
      FROM projects
      GROUP BY phase_status
    `);
    
    const statusDistribution = statusDistributionResult[0] ? statusDistributionResult[0].values.map(row => {
      return {
        status: row[0],
        count: row[1]
      };
    }) : [];
    
    // 地区分布
    const locationDistributionResult = db.exec(`
      WITH split_locations AS (
        SELECT 
          projects.id as project_id,
          trim(value) as location
        FROM 
          projects,
          json_each('["' || replace(projects.location, ',', '","') || '"]')
      )
      SELECT 
        location,
        COUNT(*) as count
      FROM 
        split_locations
      GROUP BY 
        location
    `);
    
    const locationDistribution = locationDistributionResult[0] ? locationDistributionResult[0].values.map(row => {
      return {
        location: row[0],
        count: row[1]
      };
    }) : [];
    
    return { 
      success: true, 
      data: {
        totalProjects,
        inProgressProjects,
        completedProjects,
        phaseDistribution,
        statusDistribution,
        locationDistribution
      }
    };
  } catch (error) {
    console.error('获取项目统计数据失败:', error);
    return { success: false, error: error.message };
  }
});

// 获取里程碑详情
ipcMain.handle('get-milestone', async (_, milestoneId) => {
  try {
    const db = getDb();
    
    // 获取里程碑详情
    const milestoneResult = db.exec(`SELECT * FROM milestones WHERE id = ${milestoneId}`);
    
    if (!milestoneResult[0] || milestoneResult[0].values.length === 0) {
      return { success: false, error: '里程碑不存在' };
    }
    
    // 转换里程碑数据格式
    const milestone = {};
    milestoneResult[0].columns.forEach((col, i) => {
      milestone[col] = milestoneResult[0].values[0][i];
    });
    
    // 获取所属项目信息
    const projectResult = db.exec(`
      SELECT id, name, status, phase_status FROM projects WHERE id = ${milestone.project_id}
    `);
    
    // 转换项目数据格式
    const project = {};
    if (projectResult[0] && projectResult[0].values.length > 0) {
      projectResult[0].columns.forEach((col, i) => {
        project[col] = projectResult[0].values[0][i];
      });
    }
    
    // 获取相关文档（示例数据，实际应从文档表获取）
    const documents = [];
    
    // 获取历史记录（示例数据，实际应从历史记录表获取）
    const history = [
      {
        action: '创建节点',
        description: `创建了"${milestone.name}"节点`,
        date: milestone.created_at,
        user: '系统管理员'
      }
    ];
    
    // 如果创建时间和更新时间不同，添加更新记录
    if (milestone.created_at !== milestone.updated_at) {
      history.push({
        action: '更新节点',
        description: '更新了节点信息',
        date: milestone.updated_at,
        user: '系统管理员'
      });
    }
    
    // 如果有实际完成日期，添加完成记录
    if (milestone.actual_date) {
      history.push({
        action: '完成节点',
        description: `节点已于${milestone.actual_date}完成`,
        date: milestone.actual_date,
        user: '系统管理员'
      });
    }
    
    return { 
      success: true, 
      data: {
        milestone,
        project,
        documents,
        history
      } 
    };
  } catch (error) {
    console.error('获取里程碑详情失败:', error);
    return { success: false, error: error.message };
  }
});

// 更新里程碑
ipcMain.handle('update-milestone', async (_, milestoneData) => {
  try {
    const db = getDb();
    
    // 检查里程碑是否存在
    const existingMilestoneResult = db.exec(`SELECT id FROM milestones WHERE id = ${milestoneData.id}`);
    if (!existingMilestoneResult[0] || existingMilestoneResult[0].values.length === 0) {
      return { success: false, error: '里程碑不存在' };
    }
    
    // 准备当前时间
    const now = new Date().toISOString();
    
    // 构建SQL更新语句
    const sql = `
      UPDATE milestones SET
        name = '${milestoneData.name}',
        category = '${milestoneData.category}',
        planned_date = ${milestoneData.planned_date ? `'${milestoneData.planned_date}'` : 'NULL'},
        actual_date = ${milestoneData.actual_date ? `'${milestoneData.actual_date}'` : 'NULL'},
        notes = ${milestoneData.notes ? `'${milestoneData.notes}'` : 'NULL'},
        updated_at = '${now}'
      WHERE id = ${milestoneData.id}
    `;
    
    // 执行更新
    db.exec(sql);
    
    // 保存数据库
    saveDatabase(dbPath);
    
    return { 
      success: true, 
      data: { 
        id: milestoneData.id,
        message: '里程碑更新成功'
      } 
    };
  } catch (error) {
    console.error('更新里程碑失败:', error);
    return { success: false, error: error.message };
  }
});

// 获取当前数据库路径
ipcMain.handle('get-db-path', async () => {
  try {
    // 检查当前使用的数据库路径
    const currentDbPath = db ? db.name : dbPath
    
    return { 
      success: true, 
      data: currentDbPath,
      defaultPath: dbPath
    }
  } catch (error) {
    console.error('获取数据库路径失败:', error)
    return { success: false, error: error.message }
  }
})

// 备份数据库
ipcMain.handle('backup-database', async () => {
  try {
    // 检查数据库是否已连接
    if (!db) {
      return { success: false, error: '数据库未连接' };
    }
    
    // 选择备份位置
    const result = await dialog.showSaveDialog({
      title: '选择备份位置',
      defaultPath: `military_engineering_backup_${new Date().toISOString().replace(/[:.]/g, '-')}.db`,
      filters: [
        { name: 'SQLite数据库', extensions: ['db'] },
        { name: '所有文件', extensions: ['*'] }
      ]
    });
    
    if (result.canceled) {
      return { success: false, canceled: true };
    }
    
    const backupPath = result.filePath;
    
    // 导出数据库并保存到文件
    const data = db.export();
    const buffer = Buffer.from(data);
    fs.writeFileSync(backupPath, buffer);
    
    return { success: true, message: '数据库备份成功', path: backupPath };
  } catch (error) {
    console.error('备份数据库失败:', error);
    return { success: false, error: error.message };
  }
});

// 检查管理员权限
ipcMain.handle('check-admin-rights', async () => {
  try {
    const hasAdminRights = checkAdminRights()
    return { success: true, data: hasAdminRights }
  } catch (error) {
    console.error('检查管理员权限失败:', error)
    return { success: false, error: error.message }
  }
})

// 获取项目进度更新记录
ipcMain.handle('get-progress-updates', async (_, projectId = null) => {
  try {
    const db = getDb();
    let query = `
      SELECT pu.*, p.name as project_name 
      FROM progress_updates pu
      JOIN projects p ON pu.project_id = p.id
    `;
    
    // 如果提供了项目ID，则过滤特定项目的记录
    if (projectId) {
      query += ` WHERE pu.project_id = ${projectId}`;
    }
    
    query += ` ORDER BY pu.update_date DESC`;
    
    const result = db.exec(query);
    
    // 转换结果格式
    const updates = result[0] ? result[0].values.map(row => {
      const obj = {};
      result[0].columns.forEach((col, i) => {
        obj[col] = row[i];
      });
      return obj;
    }) : [];
    
    return { success: true, data: updates };
  } catch (error) {
    console.error('获取进度更新记录失败:', error);
    return { success: false, error: error.message };
  }
});



// 获取项目里程碑
ipcMain.handle('get-project-milestones', async (_, projectId) => {
  try {
    const db = getDb();
    const query = `
      SELECT * FROM milestones
      WHERE project_id = ${projectId}
      ORDER BY actual_date DESC, planned_date DESC
    `;
    
    const result = db.exec(query);
    
    // 转换结果格式
    const milestones = result[0] ? result[0].values.map(row => {
      const obj = {};
      result[0].columns.forEach((col, i) => {
        obj[col] = row[i];
        // 将布尔值字段转换为JavaScript布尔值
        if (col === 'is_custom') {
          obj[col] = !!row[i];
        }
      });
      return obj;
    }) : [];
    
    return { success: true, data: milestones };
  } catch (error) {
    console.error('获取项目里程碑失败:', error);
    return { success: false, error: error.message };
  }
});
}

// This method will be called when Electron has finished
// initialization and is ready to create browser windows.
// Some APIs can only be used after this event occurs.
app.whenReady().then(async () => {
// Set app user model id for windows
electronApp.setAppUserModelId('com.electron.app')

// Default open or close DevTools by F12 in development
// and ignore CommandOrControl + R in production.
// see https://github.com/alex8088/electron-toolkit/tree/master/packages/utils
app.on('browser-window-created', (_, window) => {
  optimizer.watchWindowShortcuts(window)
})

// 检查是否有管理员权限
const hasAdminRights = checkAdminRights()

if (!hasAdminRights) {
  // 显示警告对话框
  dialog.showMessageBox({
    type: 'warning',
    title: '权限不足',
    message: '程序未以管理员身份运行，某些功能可能受限。',
    detail: '建议以管理员身份重新启动程序以获得完整功能。',
    buttons: ['继续', '退出'],
    defaultId: 0
  }).then(({ response }) => {
    if (response === 1) {
      // 用户选择退出
      app.quit()
    } else {
      // 继续初始化
      initializeApp()
    }
  })
} else {
  // 直接初始化
  await initializeApp()
}
})

// 将原来的初始化代码移到单独的函数中
async function initializeApp() {
try {
  // 初始化数据库
  console.log('正在初始化数据库...');
  const dbInitResult = await initDatabase();
  console.log('数据库初始化结果:', dbInitResult);
  
  // 初始化数据库API
  initDatabaseAPI();

  // IPC test
  ipcMain.on('ping', () => console.log('pong'));

  createWindow();
  
  // 添加回activate事件处理
  app.on('activate', function () {
    // On macOS it's common to re-create a window in the app when the
    // dock icon is clicked and there are no other windows open.
    if (BrowserWindow.getAllWindows().length === 0) createWindow();
  });
} catch (error) {
  console.error('应用初始化失败:', error);
  dialog.showErrorBox('初始化失败', `应用初始化过程中发生错误: ${error.message}`);
}
}

// Quit when all windows are closed, except on macOS. There, it's common
// for applications and their menu bar to stay active until the user quits
// explicitly with Cmd + Q.
app.on('window-all-closed', () => {
if (process.platform !== 'darwin') {
  app.quit()
}
})

// 应用退出前关闭数据库连接
app.on('will-quit', async () => {
try {
  // 执行自动备份
  await autoBackupDatabase()
  
  // 关闭数据库连接
  closeDatabase()
} catch (error) {
  console.error('应用退出时备份失败:', error)
  closeDatabase()
}
})

// In this file you can include the rest of your app's specific main process
// code. You can also put them in separate files and require them here.